
set more off, permanent

***Identify UK firms that experienced expansion abroad due to M&A***

*OUTSIDE DATALAB*

***merge the dynamic ownership data with final sample 
qui use "${hmrc_dir}\Round2\Data\ownership_dynamic.dta", clear
tab year
drop if year>2011|year<2005

duplicates report anon_utr related_ctry year
tab related
rename anon_utr identifier 
rename related_ctry alpha2code
rename ownership_type type2
rename related related2

*convert the 2 digit country code to 3 digit country code
merge m:1 alpha2code using "${hmrc_datadir}\CBT\ISO_countrycode.dta"
keep if _merge==3
drop _merge numeric country alpha2code

*merge with final regression sample
count if alpha3code=="GBR"
drop if alpha3code=="GBR"
duplicates report identifier alpha3code year


merge 1:m identifier alpha3code year using "${hmrc_datadir}\export_reg_final_ukmncs_fy" /*using the new ownership info if firms had M&A in Zephyr*/
drop if _merge==1 /*firms only in the new ownership data but not in the trade data*/
replace related2=related if _merge==2 /*firms only in the trade data but not new ownership data: using their existing ownership info*/

drop _merge

label variable related2 "dynamic ownership"

tab type2
tab related2
tab related
save "${hmrc_datadir}\export_reg_final_ukmncs_fy_dynamicownership", replace

********************count changed firm country pairs****************************
preserve
keep identifier year related2 alpha3code
duplicates drop
*total number of firm-country pair changed
tab related2
*number of firm-country pair changed per year:
sort identifier alpha3code year
bysort identifier alpha3code: gen i=_n
sort identifier alpha3code year
bysort identifier alpha3code: gen change= (related2!=related2[_n-1]) & i!=1
bysort year: egen no_change_year=sum(change)
*number of firms established new relation per year:
bysort identifier year: egen firm_change=max(change)
bysort year: egen no_firmchange=sum(firm_change)
*number of coutry established relation with a new UK company per year:
bysort alpha3code year: egen ctry_change=max(change)
bysort year: egen no_ctrychange=sum(ctry_change)
keep no_ctrychange no_firmchange no_change_year year
duplicates drop
save "${hmrc_datadir}\number of changed ownership per year", replace
export excel "${hmrc_datadir}\number of changed ownership per year.xls", firstrow(var) replace
restore 
